﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSharp.Infrastructure.Sql
{
    public class MsSqlBigDALHelper
    {
        private string connectionString = string.Empty;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">
        /// 批量操作需要包含 charset=utf8;AllowLoadLocalInfile=true
        /// </param>
        public MsSqlBigDALHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public int ReadItemCount(string table_name)
        {
            string sql = string.Format(@"SELECT count(0) FROM [{0}];", table_name);
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
        }

        public bool DateSetInsert(DataTable dt)
        {
            try
            {
                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(connectionString))           // 引用SqlBulkCopy  
                {
                    sqlRevdBulkCopy.DestinationTableName = "[" + dt.TableName + "]";                // 数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dt.Rows.Count;                    // 有几行数据  
                    sqlRevdBulkCopy.WriteToServer(dt);                              // 数据导入数据库  
                    sqlRevdBulkCopy.Close();                                            // 关闭连接  
                    return true;
                }
            }
            catch (Exception ex)
            {
                Logs.WriteLog(LogType.Error, "数据入库异常:" + ex.ToString());
                return false;
            }

        }

        /// <summary>
        /// 查询指定条数
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="maxId">最大</param>
        /// <param name="size">一次查询条数</param>
        /// <returns>datatable便于导出</returns>
        public DataTable QueryInfo(string sql, List<SqlParameter> list)
        {
            //实例化sql连接 外呼数据库
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (list != null)
                    {
                        foreach (var p in list)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }

                    using (DataSet ds = new DataSet())
                    {
                        SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
                        sqlda.Fill(ds);

                        return ds.Tables[0];
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="connection">
        /// 链接字符串
        /// eg:mssql uid=sa; pwd=test; database=test; server=localhost;
        /// </param>
        /// <param name="sql_str"></param>
        public int ExecuteSql(string sql_str)
        {
            using (SqlConnection sqlcn = new SqlConnection(connectionString))
            {
                sqlcn.Open();
                using (SqlCommand cmd = new SqlCommand(sql_str, sqlcn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
